1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Windows.Forms;
9 using System.Data.SqlClient;
10 using Excel = Microsoft.Office.Interop.Excel;
11 namespace WarehouseManagementSystem
12 {
13 public partial class frmStockRecord1 : Form
14 {
15 SqlDataReader rdr = null;
16 SqlConnection con = null;
17 SqlCommand cmd = null;
18 ConnectionString cs = new ConnectionString();
19 public frmStockRecord1()
20 {
21 InitializeComponent();
22 }
23 public void GetData()
24 {
25 try
26 {
27 con = new SqlConnection(cs.DBConn);
28 con.Open();
29 String sql = "SELECT RTRIM(StockID),RTRIM(StockDate),RTRIM(Product.ProductID),RTRIM(ProductName),RTRIM(Features),RTRIM(Supplier.SupplierID),RTRIM(SupplierName),RTRIM(Quantity),RTRIM(ExpiryDate) from Stock,Product,Supplier where Stock.ProductID=Product.ProductID and Stock.SupplierID=Supplier.SupplierID order by ProductName";
30 cmd = new SqlCommand(sql, con);
31 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
32 dataGridView1.Rows.Clear();
33 while (rdr.Read() == true)
34 {
35 dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5],rdr[6],rdr[7],rdr[8]);
36 }
37 con.Close();
38 }
39 catch (Exception ex)
40 {
41 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
42 }
43 }
44 private void frmStockRecord_Load(object sender, EventArgs e)
45 {
46 GetData();
47 }
48
49 private void txtProductname_TextChanged(object sender, EventArgs e)
50 {
51 try
52 {
53 con = new SqlConnection(cs.DBConn);
54 con.Open();
55 String sql = "SELECT RTRIM(StockID),RTRIM(StockDate),RTRIM(Product.ProductID),RTRIM(ProductName),RTRIM(Features),RTRIM(Supplier.SupplierID),RTRIM(SupplierName),RTRIM(Quantity),RTRIM(ExpiryDate) from Stock,Product,Supplier where Stock.ProductID=Product.ProductID and Stock.SupplierID=Supplier.SupplierID and productname like '" + txtProductname.Text + "%' order by ProductName";
56 cmd = new SqlCommand(sql, con);
57 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
58 dataGridView1.Rows.Clear();
59 while (rdr.Read() == true)
60 {
61 dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6],rdr[7],rdr[8]);
62 }
63 con.Close();
64 }
65 catch (Exception ex)
66 {
67 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
68 }
69 }
70
71 private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
72 {
73 string strRowNumber = (e.RowIndex + 1).ToString();
74 SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
75 if (dataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
76 {
77 dataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
78 }
79 Brush b = SystemBrushes.ControlText;
80 e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
81
82 }
83
84 private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
85 {
86 try
87 {
88 DataGridViewRow dr = dataGridView1.SelectedRows[0];
89 this.Hide();
90 frmStock frm = new frmStock();
91 frm.Show();
92 frm.txtStockID.Text = dr.Cells[0].Value.ToString();
93 frm.dtpStockDate.Text = dr.Cells[1].Value.ToString();
94 frm.txtProductID.Text = dr.Cells[2].Value.ToString();
95 frm.txtProductName.Text = dr.Cells[3].Value.ToString();
96 frm.txtFeatures.Text = dr.Cells[4].Value.ToString();
97
98 frm.txtSupplierID.Text = dr.Cells[5].Value.ToString();
99 frm.cmbSupplierName.Text = dr.Cells[6].Value.ToString();
100 frm.txtQty.Text = dr.Cells[7].Value.ToString();
101 frm.txtQty1.Text = dr.Cells[7].Value.ToString();
102 frm.dtpExpiryDate.Text = dr.Cells[8].Value.ToString();
103 frm.btnUpdate.Enabled = true;
104 frm.btnDelete.Enabled = true;
105 frm.btnSave.Enabled = false;
106 frm.lblUser.Text = lblUser.Text;
107 frm.lblUserType.Text = lblUserType.Text;
108 }
109
110 catch (Exception ex)
111 {
112 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
113 }
114 }
115
116 private void frmStockRecord_FormClosing(object sender, FormClosingEventArgs e)
117 {
118 this.Hide();
119 frmStock frm = new frmStock();
120 frm.lblUser.Text = lblUser.Text;
121 frm.lblUserType.Text = lblUserType.Text;
122 frm.Show();
123 }
124
125 private void Button4_Click(object sender, EventArgs e)
126 {
127 int rowsTotal = 0;
128 int colsTotal = 0;
129 int I = 0;
130 int j = 0;
131 int iC = 0;
132 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
133 Excel.Application xlApp = new Excel.Application();
134
135 try
136 {
137 Excel.Workbook excelBook = xlApp.Workbooks.Add();
138 Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets[1];
139 xlApp.Visible = true;
140
141 rowsTotal = dataGridView1.RowCount;
142 colsTotal = dataGridView1.Columns.Count - 1;
143 var _with1 = excelWorksheet;
144 _with1.Cells.Select();
145 _with1.Cells.Delete();
146 for (iC = 0; iC <= colsTotal; iC++)
147 {
148 _with1.Cells[1, iC + 1].Value = dataGridView1.Columns[iC].HeaderText;
149 }
150 for (I = 0; I <= rowsTotal - 1; I++)
151 {
152 for (j = 0; j <= colsTotal; j++)
153 {
154 _with1.Cells[I + 2, j + 1].value = dataGridView1.Rows[I].Cells[j].Value;
155 }
156 }
157 _with1.Rows["1:1"].Font.FontStyle = "Bold";
158 _with1.Rows["1:1"].Font.Size = 12;
159
160 _with1.Cells.Columns.AutoFit();
161 _with1.Cells.Select();
162 _with1.Cells.EntireColumn.AutoFit();
163 _with1.Cells[1, 1].Select();
164 }
165 catch (Exception ex)
166 {
167 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
168 }
169 finally
170 {
171 //RELEASE ALLOACTED RESOURCES
172 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
173 xlApp = null;
174 }
175 }
176
177 private void button2_Click(object sender, EventArgs e)
178 {
179 txtProductname.Text = "";
180 dtpStockDateFrom.Text = System.DateTime.Today.ToString();
181 dtpStockDateTo.Text = System.DateTime.Today.ToString();
182 GetData();
183 }
184
185 private void button1_Click(object sender, EventArgs e)
186 {
187 try
188 {
189 con = new SqlConnection(cs.DBConn);
190 con.Open();
191 String sql = "SELECT RTRIM(StockID),RTRIM(StockDate),RTRIM(Product.ProductID),RTRIM(ProductName),RTRIM(Features),RTRIM(Supplier.SupplierID),RTRIM(SupplierName),RTRIM(Quantity),RTRIM(ExpiryDate) from Stock,Product,Supplier where Stock.ProductID=Product.ProductID and Stock.SupplierID=Supplier.SupplierID and StockDate between @d1 and @d2 order by ProductName";
192 cmd = new SqlCommand(sql, con);
193 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "StockDate").Value = dtpStockDateFrom.Value.Date;
194 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "StockDate").Value = dtpStockDateTo.Value.Date;
195 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
196 dataGridView1.Rows.Clear();
197 while (rdr.Read() == true)
198 {
199 dataGridView1.Rows.Add(rdr[0], rdr[1], rdr[2], rdr[3], rdr[4], rdr[5], rdr[6], rdr[7],rdr[8]);
200 }
201 con.Close();
202 }
203 catch (Exception ex)
204 {
205 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
206 }
207 }
208 }
209 }
